clear all
set more off

cd "$path"


use "Data/non-proprietary/Geocoding_Result"
ren chiave_age chiave_agenzia
keep Loc_name Status Score x y chiave codice comune provincia
ren x longitude 
ren y latitude
ren Loc_name flag
sort chiave_agenzia codiceabi
save temp, replace 



* Skip to temp_master
use "Data/proprietary/00 Merged Data", clear
sort chiave_agenzia

merge m:1 chiave_agenzia using temp
tab _m
keep if _m==3
drop _m


**** Merge to add cash risk
ren rischio_contanti cash_risk
ren numero_dipendenti number_employees


* Characteristics
lab var device_characteristic1 "BOX PER L'OPERATORE "
lab var device_characteristic2 "BUSSOLA MONOPERSONA "
lab var device_characteristic3 "BUSSOLA PLURIPERSONA "
lab var device_characteristic4 "DISPOSITIVI BIOMETRICI "
lab var device_characteristic5 "METAL DETECTOR "
lab var device_characteristic6 "PORTA SEMPLICE(APERTURA MANUALE) "
lab var device_characteristic7 "RICONOSCIMENTO DEL VOLTO "
lab var device_characteristic8 "BOX PER L'OPERATORE "
lab var device_characteristic9 "BUSSOLA MONOPERSONA "
lab var device_characteristic10 "BUSSOLA PLURIPERSONA "
lab var device_characteristic11 "DISPOSITIVI BIOMETRICI "
lab var device_characteristic12 "METAL DETECTOR "
lab var device_characteristic13 "PORTA SEMPLICE(APERTURA MANUALE) "
lab var device_characteristic14 "RICONOSCIMENTO DEL VOLTO "
lab var device_characteristic15 "BARRIERE ESTERNE "
lab var device_characteristic16 "INFERRIATE/SERRANDE "
lab var device_characteristic17 "PROTEZIONE ATTIVA INFISSI PERIMETRALI "
lab var device_characteristic18 "PROTEZIONE ATTIVA MURI PERIMETRALI "
lab var device_characteristic19 "VETRATE ANTIPROIETTILE/ANTISFONDAMENTO "
lab var device_characteristic20 "VIDEOREGISTRAZIONE "
lab var device_characteristic21 "VIDEOSORVEGLIANZA "
lab var device_characteristic22 "BANCONE BLINDATO "
lab var device_characteristic23 "EROGATORE AUTOMATICO DI BANCONOTE(CASH IN/CASH OUT) "
lab var device_characteristic24 "SCOMPARTI MULTIPLI TEMPORIZZATI(FRAZION. DEL CONTANTE) "
lab var device_characteristic25 "SCOMPARTI SEMPLICI TEMPORIZZATI "
lab var device_characteristic26 "SISTEMA MACCHIATORE DI BANCONOTE "
lab var device_characteristic27 "SISTEMA TRACCIABILITA' BANCONOTE "
lab var device_characteristic28 "AUTOMATICO "
lab var device_characteristic29 "COLLEGAMENTO A CENTRALE D`ALLARME AZIENDALE "
lab var device_characteristic30 "COLLEGAMENTO A CENTRALE D`ALLARME ISTITUTO DI VIGILANZA "
lab var device_characteristic31 "COLLEGAMENTO A DISPOSITIVI D`ALLARME LOCALI (OTTICI/ACUST.) "
lab var device_characteristic32 "COLLEGAMENTO ALLE FORZE DELL`ORDINE "
lab var device_characteristic33 "MANUALE "
lab var device_characteristic34 "DOPPIA CHIAVE "
lab var device_characteristic35 "TIME BINATION "
lab var device_characteristic36 "TIME LOCK "
lab var device_characteristic37 "DOPPIA CHIAVE "
lab var device_characteristic38 "TIME BINATION "
lab var device_characteristic39 "TIME LOCK "
lab var device_characteristic40 "INSERITO IN CASSAFORTE "
lab var device_characteristic41 "INSERITO IN CAVEAU "
lab var device_characteristic42 "ANCORAGGIO ANTISTRAPPO "
lab var device_characteristic43 "CHIAVI IN MANO A SOCIETA' DI TRASPORTO VALORI "
lab var device_characteristic44 "COLLEGATO AD ALLARME "
lab var device_characteristic45 "MACCHIATORE VALORI "
lab var device_characteristic46 "SISTEMA ANTIESPLOSIONE "
lab var device_characteristic47 "TEMPORIZZAZIONE "
lab var device_characteristic48 "ANCORAGGIO ANTISTRAPPO "
lab var device_characteristic49 "CHIAVI IN MANO A SOCIETA' DI TRASPORTO VALORI "
lab var device_characteristic50 "COLLEGATO AD ALLARME "
lab var device_characteristic51 "MACCHIATORE VALORI "
lab var device_characteristic52 "SISTEMA ANTIESPLOSIONE "
lab var device_characteristic53 "SISTEMA MACCHIATORE DI BANCONOTE "
lab var device_characteristic54 "TEMPORIZZAZIONE" 

egen sum_devices=rowtotal(device_*)
egen ninvisible=rowtotal(device_characteristic53 device_characteristic52 device_characteristic51 /*
*/device_characteristic44-device_characteristic45 device_characteristic23-device_characteristic32)
gen nvisible=sum_devices-ninvisib


keep device* chiave_agenzia year longit lati flag haul unsuccessful attempted codiceabi guardia ninvi nvis comune provincia
sort chiave_agenzia year 
tempfile temp_merge
save `temp_merge', replace


keep chiave_agenzia year longit lati
tempfile temp
save `temp', replace

/*bysort year chiave_agenzia x y: gen temp=_n==1
gen id_year=sum(temp)
drop temp*/

forval y=2000/2010 {
 use `temp' if year==`y', clear
 tempfile temp`y'
 save `temp`y'', replace 
 qui geonear chiave_agenzia longit lati using "`temp`y''", n(chiave longit lati) ignoreself nearcount(10) wide
 save `temp`y'', replace
}


clear
forval y=2000/2010 {
 append using `temp`y''
}
 

drop longit lati
reshape long nid km_to_nid, i(chiave_agenzia year) j(neighbor)
sort chiave_agenzia year
merge chiave_agenzia year using `temp_merge'
tab _m
drop _m

* Rename variable for i
foreach y of varlist unsuccessful attempted codiceabi guardia nvisible ninvisible chiave_agenzia flag{
ren `y' `y'1
}
ren nid chiave_agenzia

sort chiave_agenzia year
merge chiave_agenzia year using `temp_merge'
tab _m
drop if _m==2
drop _m

* Rename variable for i
foreach y of varlist unsuccessful attempted codiceabi guardia nvisible ninvisible chiave_agenzia flag{
ren `y' `y'2
}

local listv "unsuccessful attempted guardia"
foreach y of local listv{
replace `y'1=0 if `y'1 ==.
replace `y'2=0 if `y'2 ==.
}

bysort provincia: gen xprov=_n==1
gen idprov=sum(xprov)
drop xprov 

/*
sort codiceabi1 year
by codiceabi1 year: gen mguardia1=sum(guardia1)
by codiceabi1 year: replace mguardia1=(mguardia1[_N]-guardia1)/(_N-1)
lab var mguardia1 "Av. # guards by ABI (branch i)"

by codiceabi1 year: gen mnvisible1=sum(nvisible1)
by codiceabi1 year: replace mnvisible1=(nvisible1[_N]-nvisible1)/(_N-1)
lab var mnvisible1 "Visible devices by ABI  (branch i)"

by codiceabi1 year: gen mninvisible1=sum(ninvisible1)
by codiceabi1 year: replace mninvisible1=(ninvisible1[_N]-ninvisible1)/(_N-1)
lab var mninvisible1 "Invisible devices by ABI  (branch i)"

sort codiceabi2 year
by codiceabi2 year: gen mguardia2=sum(guardia2)
by codiceabi2 year: replace mguardia2=(mguardia2[_N]-guardia2)/(_N-1)
lab var mguardia2 "Av. # guards by ABI  (branch j)"

by codiceabi2 year: gen mnvisible2=sum(nvisible2)
by codiceabi2 year: replace mnvisible2=(nvisible2[_N]-nvisible2)/(_N-1)
lab var mnvisible1 "Visible devices by ABI  (branch j)"

by codiceabi2 year: gen mninvisible2=sum(ninvisible2)
by codiceabi2 year: replace mninvisible2=(ninvisible1[_N]-ninvisible2)/(_N-1)
lab var mninvisible1 "Invisible devices by ABI  (branch j)"
*/

drop if nvisible1+ninvisible1==0 /* these are banks without any devices, which is unlikely*/

compress
save "$path/Data/proprietary/01 Master Data.dta", replace

